<?php

define("_AXIS_ROW"                            ,   0);
define("_AXIS_COL"                            ,   1);
define("_IMPORT_TYPE_FILE"                    , "FILE");
define("_IMPORT_TYPE_XLS"                     , "XLS");
define("_XLS_DATA_DEFAULT_START"              ,   1);
define("_XLS_DB_FIELD_TYPE"                   ,   1);
define("_XLS_DB_FIELD"                        ,   1);
define("_XLS_DB_FIELD_DATA"                   ,   2);
define("_XLS_FIELD_AXIS"                      ,   2);
define("_XLS_FIELD_HDR_AXIS"                  ,   2);
define("_XLS_FIELD_DTL_AXIS"                  ,   3);
define("_XLS_FIELD_SUB_DTL_AXIS"              ,   4);
define("_XLS_AXIS_DATA"                       ,   2);
define("_COMMA_SEPARATOR"                     ,  ",");

class ImportToDb{

  /**
   * Database handler
   *
   * @var Database
   */
  public $Db;

  /**
   * error handler
   *
   * @var Misc
   */
  public $ErrHdl;

  /**
   * File handler
   *
   * @var File
   */
  public $File;

  /**
   * Xls handler
   *
   * @var Spreadsheet_Excel_Reader
   */
  public $Excel;

  /**
   * source file of list of field
   *
   * @var string
   */
  public $srcFileField;

  /**
   * source file of list of field type
   *
   * @var string
   */
  public $srcFileFieldType;

  /**
   * source file of list of data
   *
   * @var string
   */
  public $srcFileData;

  /**
   * source Xls of list of data
   *
   * @var string
   */
  public $srcXlsData;

  /**
   * the type of import
   *
   * @var string
   */
  public $importType;

  /**
   * @var integer
   */
  public $szField;

  /**
   * @var array
   */
  public $lstField;

  /**
   * @var integer
   */
  public $szFieldType;

  /**
   * @var array
   */
  public $lstFieldType;

  /**
   * @var integer
   */
  public $szData;

  /**
   * @var array
   */
  public $lstData;

  /**
   * @var integer
   */
  public $szDtlAxis;

  /**
   * @var array
   */
  public $lstDtlAxis;

  /**
   * query helpers
   *
   * @var Composer
   */
  public $ComposerQuery;

  /**
   * init
   *
   * @param Database                  $Db
   * @param string                    $importType
   * @param Misc                      $ErrHdl
   * @param Composer                  $ComposerQuery
   * @param File                      $File
   * @param Spreadsheet_Excel_Reader  $Excel
   */
  function init(&$Db,
                 $importType,
                 $ErrHdl,
                 $ComposerQuery,
                 $File,
                 $Excel){

    $this->Db               = &$Db;
    $this->importType       = &$importType;
    $this->ErrHdl           = &$ErrHdl;
    $this->ComposerQuery    = &$ComposerQuery;
    $this->File             = &$File;
    $this->Excel            =  $Excel;
  }

  /**
   * read the DB field from file
   * It also shows you how to insert
   *
   * @param string  $srcFile
   * @param string  $defaultSeparator
   */
  function readFileField($srcFile,$defaultSeparator){

    $File = &$this->File;

    $File->openFile($srcFile, "r");

    $File->readFile();

    $cntFileFieldBuffer =  $File->getReadContent();

    $cntFileFieldRow    =  $File->getReadContentRow();

    if($cntFileFieldRow > 0){

      $this->lstField = explode($defaultSeparator,
                                $cntFileFieldBuffer[0]);
    }else{

      $this->ErrHdl->usrDie("Error: Field is not defined or invalid");
    }

    $this->szField = sizeof($this->lstField);

    $File->close();
  }

  /**
   * read the DB field from XLS
   * It also shows you how to insert
   *
   * @param Spreadsheet_Excel_Reader $Excel
   * @param string                   $srcFile
   */
  function readXlsField($Excel, $srcFile){

    #$Excel = $this->Excel;

    $Excel->read($srcFile);

    /**
     * fetch field identifier
     */
    $this->lstField   = explode(_DEFAULT_SEPARATOR_02,
                                $Excel->sheets[0]['cells']
                                                 [_XLS_DB_FIELD][_XLS_DB_FIELD_DATA]);

    $this->szField    = sizeof($this->lstField);

    /**
     * fetch detail axis
     */
    $this->lstDtlAxis = explode(_COMMA_SEPARATOR,
                                $Excel->sheets[0]['cells']
                                                 [_XLS_FIELD_AXIS][_XLS_AXIS_DATA]);

    $this->szDtlAxis  = sizeof($this->lstDtlAxis);
  }

  /**
   * read the DB field type from file
   * It also shows you how to insert
   *
   * @param string  $srcFile
   * @param string  $defaultSeparator
   */
  function readFileFieldType($srcFile,$defaultSeparator){

    $File = &$this->File;

    $File->openFile($srcFile, "r");

    $File->readFile();

    $cntFileFieldBuffer =  $File->getReadContent();

    $cntFileFieldRow    =  $File->getReadContentRow();

    if($cntFileFieldRow > 0){

      $this->lstFieldType = explode($defaultSeparator,
                                    $cntFileFieldBuffer[0]);
    }else{

      $this->ErrHdl->usrDie("Error: Field is not defined or invalid");
    }

    $this->szFieldType  = sizeof($this->lstFieldType);

    $File->close();
  }

  /**
   * read the DB field type from XLS
   * It also shows you how to insert
   *
   * @param Spreadsheet_Excel_Reader $Excel
   * @param string                   $srcFile
   */
  function readXlsFieldType($Excel,$srcFile){

    #$Excel = $this->Excel;

    $Excel->read($srcFile);

    /**
     * fetch field identifier
     */
    $this->lstFieldType   = explode(_DEFAULT_SEPARATOR_02,
                                    $Excel->sheets[0]['cells']
                                                     [_XLS_DB_FIELD_TYPE][_XLS_DB_FIELD_DATA]);

    $this->szFieldType    = sizeof($this->lstFieldType);
  }

  /**
   * read the data field
   * Its organisation depends on
   * how DB field has been defined
   *
   * @param string $defaultSeparator
   */
  function readFileData($defaultSeparator){


  }

  /**
   * Import all data to defined Db and field
   *
   * @param string  $srcFile
   * @param string  $defaultSeparator
   */
  function import($Excel,
                  $srcFile,
                  $defaultSeparator){

    switch($this->importType){

      case _IMPORT_TYPE_FILE:

        $this->importFile($srcFile,$defaultSeparator);
        break;

      case _IMPORT_TYPE_XLS:

        $this->importXls($Excel,$srcFile);
        break;

      default:
        $this->ErrHdl->usrDie("Error: Unknown Import Type");
    }
  }

  /**
   * import based from file
   *
   * @param string  $srcFile
   * @param string  $defaultSeparator
   */
  function importFile($srcFile,$defaultSeparator){

    $Db   = &$this->Db;

    $File = &$this->File;

    $File->openFile($srcFile, "r");

    $File->readFile();

    $cntFileDataBuffer =  $File->getReadContent();

    $cntFileDataRow    =  $File->getReadContentRow();

    if($cntFileDataRow > 0){

      /**
       * foreach fetched FileData
       * Do insert into DB
       */
      for($iRow = 0; $iRow < $cntFileDataRow ; $iRow++){

        $this->lstData = explode(_DEFAULT_SEPARATOR_02,
                                 $cntFileDataBuffer[$iRow]);

        $this->szData  = sizeof($this->lstData);

        $this->ComposerQuery->insertExecQuery($Db,
                                              $this->lstField,
                                              $this->szField,
                                              $this->lstFieldType,
                                              $this->szFieldType,
                                              $this->lstData,
                                              $this->szData);
      }

      if($Db->getStatus() == true){

        $this->ErrHdl->usrMsg("Upload Complete");
      }else{

        $this->ErrHdl->usrMsg("Upload Process Interupted");
      }

      $Db->dc();
    }else{

      $this->ErrHdl->usrDie("Error: Field is not defined or invalid");
    }

    $File->close();
  }

  /**
   * import from excel
   *
   * @param Spreadsheet_Excel_Reader $Excel
   * @param string                   $srcFile
   */
  function importXls($Excel,$srcFile){

    $Db   = &$this->Db;

    $Excel->read($srcFile);

    /**
     * fetch axis of data
     */
    if($this->szDtlAxis == 1){

      /**
       * extract the axis
       */
      $axis = explode(_DEFAULT_SEPARATOR_02,
                      $this->lstDtlAxis[0]);

      /**
       * we only have one axis
       * it means this axis
       * is the start point
       */
      $next = true;
      $iRow = $axis[_AXIS_ROW];

      while($next){

        /**
         * if we hit the bottom of data
         * bailed out
         */
        if(!$Excel->sheets[0]['cells']
                            [$iRow][$axis[_AXIS_COL]]){

          $next = false;
        }else{

          $idx    = _XLS_DATA_DEFAULT_START;

          $maxCol = $this->szField + $axis[_AXIS_COL];

          for($iCol = $axis[_AXIS_COL]; $iCol < $maxCol; $iCol++){

            $this->lstData[$idx] = $Excel->sheets[0]['cells']
                                                    [$iRow][$iCol];

            $idx++;
          }

          $this->szData = sizeof($this->lstData);

          $this->ComposerQuery->insertExecQuery($Db,
                                                $this->lstField,
                                                $this->szField,
                                                $this->lstFieldType,
                                                $this->szFieldType,
                                                $this->lstData,
                                                $this->szData);
        }

        $iRow++;
      }
    }else
    if($this->szDtlAxis >  1){

      /**
       * check if total axis is equal to field size
       * since the szField size should be the same with szDtlAxis
       */
      if($this->szDtlAxis != $this->szField){

        $this->ErrHdl->usrDie("Error: Axis and fields mismatch");
      }else{

        /* Do nothing */
      }

      /**
       * forEach axis
       */
      $idx = _XLS_DATA_DEFAULT_START;

      for($iAxis = 0; $iAxis < $this->szDtlAxis ; $iAxis++){

        /**
         * extract the axis
         */
        $axis = explode(_DEFAULT_SEPARATOR_02,
                        $this->lstDtlAxis[$iAxis]);

        $this->lstData[$idx] = $Excel->sheets[0]['cells']
                                                [$axis[_AXIS_ROW]][$axis[_AXIS_COL]];

        $idx++;
      }

      $this->szData = sizeof($this->lstData);

      $this->ComposerQuery->insertExecQuery($Db,
                                            $this->lstField,
                                            $this->szField,
                                            $this->lstFieldType,
                                            $this->szFieldType,
                                            $this->lstData,
                                            $this->szData);
    }else{

      /*Do nothing*/
    }
  }
}
?>